package cn.com.bluemoon.daps.model.utils;

import cn.com.bluemoon.daps.common.domain.ResultBean;
import cn.com.bluemoon.daps.common.enums.DatabaseType;
import cn.com.bluemoon.daps.common.sql.SqlScriptUtils;
import cn.com.bluemoon.daps.model.core.file.entry.ResultEntryImpl;
import cn.com.bluemoon.daps.model.dto.ModelTableInfoDto;
import cn.com.bluemoon.daps.model.dto.SubordinateKeyDto;
import cn.com.bluemoon.daps.model.entity.DapDataModelSubordinateKey;
import cn.com.bluemoon.daps.model.entity.DapDataModelTableField;
import cn.com.bluemoon.daps.model.entity.DapDataModelTablePartition;
import com.google.common.collect.Lists;
import io.swagger.models.auth.In;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.time.DateUtils;

import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class SqlUtils {

    private static List<String> markList = Arrays.asList("\"\"", "\"", "\'\'", "\'",
            "“”", "”", "“", "‘’", "’", "‘");
    private static List<String> dateList = Arrays.asList("timestamp","datetime");

    public static final String[] PARSE_DATE_TIME_PATTERN = {
            "yyyy-MM-dd hh:mm:ss",
            "YYYY-MM-dd hh:mm:ss",
            "yyyy-MM-dd'T'HH:mm:ss.SSS",
            "yyyy-MM-dd'T'HH:mm:ss.SSSZ"
    };

    public static String createMysqlOrPg(ModelTableInfoDto modelTableInfoDto, List<SubordinateKeyDto> keys, DatabaseType type){

        // 表schema
        String schema = StringUtils.isNotBlank(modelTableInfoDto.getTableSchema()) && type.equals(DatabaseType.PGSQL)
                ? modelTableInfoDto.getTableSchema()+"." : "";
        StringBuffer buffer = new StringBuffer("create table ").append(schema);
        String single = type.equals(DatabaseType.MYSQL) ? "`" : "";
        // 存储主键字段名
        List<String> list = Lists.newArrayList();
        buffer.append(single).append(modelTableInfoDto.getEngName()).append(single).append("(");
        // 主键自增值
        Long autoIncrDefaultValue = null;
        StringBuffer pgFieldComment = new StringBuffer();
        // 字段表为空直接返回
        if(modelTableInfoDto.getFieldList().isEmpty()){ return buffer.append(")").toString(); }
        for (DapDataModelTableField field : modelTableInfoDto.getFieldList()) {
            buffer.append(single).append(field.getFieldName()).append(single).append(" ").append(field.getDataType());
            // 小数位不为空
            if(!isNull(field.getDecimalLength()) && StringUtils.isNotBlank(field.getLength())){
                buffer.append("(").append(field.getLength()).append(",").append(field.getDecimalLength()).append(")");
            }else {
                buffer.append(StringUtils.isNotBlank(field.getLength()) ? "(" + field.getLength() + ") " : " ");
            }
            // 数值类型
            if(isNumber(field.getDataType())){
                // 无符号
                String unSigned = isNull(field.getUnSigned()) ? "" : "UNSIGNED ";
                // 填充零
                String zeroFill = isNull(field.getZeroFill()) ? "" : "ZEROFILL ";
                buffer.append(unSigned).append(zeroFill);
            }
            // 有主键
            if (!isNull(field.getPrimaryKey()) && type.equals(DatabaseType.MYSQL)) {
                list.add(field.getFieldName());
                buffer.append("NOT NULL ");
            }
            // 自增默认值
            if(field.getAutoIncrDefaultValue() != null && !isNull(field.getAutoIncrement())){
                autoIncrDefaultValue = field.getAutoIncrDefaultValue();
            }
            // 为空
            if (isNull(field.getNotNull())) {
                // 不是主键的情况下， 默认为空
                // String string = markList.contains(field.getDefaultValue()) ? "\'\'" : "'" + field.getDefaultValue() + "'";
                String defaultValue = StringUtils.isNotBlank(field.getDefaultValue()) ?
                        getDefaultValue(field.getDataType(), field.getDefaultValue()) : "NULL";
                String isNull = isNull(field.getPrimaryKey()) ? "DEFAULT " + defaultValue + " " : " ";
                buffer.append(type.equals(DatabaseType.MYSQL) ? isNull : "DEFAULT NULL ");
            } else {
                // 不为空，不是主键
                String isNull = isNull(field.getPrimaryKey()) ? "NOT NULL " : " ";
                String defaultValue = StringUtils.isNotBlank(field.getDefaultValue()) ? "DEFAULT "
                        + getDefaultValue(field.getDataType(), field.getDefaultValue()) + " " : "";
                buffer.append(type.equals(DatabaseType.MYSQL) ? isNull : "NOT NULL ").append(defaultValue);
            }
            // 是否自增
            String auto = isNull(field.getAutoIncrement()) ? "" : "AUTO_INCREMENT ";
            if(type.equals(DatabaseType.MYSQL)){
                buffer.append(auto).append("COMMENT ").append("'"+ SqlScriptUtils.formatComment(field.getComment())+"',");
            }else{
                buffer.append(",");
                pgFieldComment.append("comment on column ").append(schema).append(modelTableInfoDto.getEngName()).append(".")
                        .append(field.getFieldName()).append(" is ").append("'").append( SqlScriptUtils.formatComment(field.getComment())).append("';");
            }
        }
        // 主键索引
        if(!list.isEmpty()){
            String collect = list.stream().collect(Collectors.joining(","));
            buffer.append("PRIMARY KEY ").append("(").append(collect).append("),");
        }
        // 创建索引
        StringBuffer sql = createIndex(buffer, keys, single);
        // 判断自增初始值是否为空
        sql.append(") ").append(autoIncrDefaultValue != null ? "AUTO_INCREMENT=" + autoIncrDefaultValue : "");
        // 如果存在
        String string = SqlScriptUtils.formatComment(modelTableInfoDto.getName());
        String mysqlComment = " COMMENT ";
        String pgComment = "; COMMENT on table "+ schema + modelTableInfoDto.getEngName() + " is ";
        sql.append(type.equals(DatabaseType.MYSQL) ? mysqlComment : pgComment).append("'"+string+"';");
        if(type.equals(DatabaseType.PGSQL)){
            sql.append(pgFieldComment);
        }
        return sql.toString();
    }

    /**
     * 获取默认值
     * @param dataType
     * @param defaultValue
     * @return
     */
    public static String getDefaultValue(String dataType, String defaultValue){
        // 如果为时间类型
        if(dateList.contains(dataType)){
            return checkIsDateTime(defaultValue) ? "'" + defaultValue + "'" : defaultValue;
        }else{
            return markList.contains(defaultValue) ? "\'\'" : "'" + defaultValue + "'";
        }
    }
    /**
     * 创建索引
     * @param buffer
     * @param keys
     * @return
     */
    public static StringBuffer createIndex(StringBuffer buffer, List<SubordinateKeyDto> keys, String single) {
        // 如果索引表为空
        if(keys.isEmpty()){ return buffer.deleteCharAt(buffer.length() - 1); }
        // 对索引列表进行加工
        List<DapDataModelSubordinateKey> list = getFieldData(keys, single);
        // 首先获得索引名
        for ( DapDataModelSubordinateKey key : list) {
            // 根据索引类型进行判断
            buffer.append(key.getKeyType() == 1 || key.getKeyType() == 2 ? "" : "UNIQUE ").
                    append("KEY ").append(single).append(key.getKeyName()).append(single).append(" (").append(key.getFieldIds()).append(")");
            buffer.append(key.getKeyMethod() == 1 ? "" : key.getKeyMethod() == 2 ? " USING BTREE" : " USING HASH")
                    .append(",");
        }
        buffer.deleteCharAt(buffer.length() - 1);
        return buffer;
    }

    /**
     * 对单个索引引用多个字段进行加工
     * @param keys
     */
    public static List<DapDataModelSubordinateKey> getFieldData(List<SubordinateKeyDto > keys, String single) {

        // 遍历索引列表集合
        List<DapDataModelSubordinateKey> list = new ArrayList<>();
        for (SubordinateKeyDto dto : keys) {
            DapDataModelSubordinateKey key = new DapDataModelSubordinateKey();
            key.setId(dto.getId());
            key.setKeyName(dto.getKeyName());
            key.setKeyChinName(dto.getKeyChinName());
            key.setKeyMethod(dto.getKeyMethod());
            key.setKeyType(dto.getKeyType());
            if(StringUtils.isNotBlank(dto.getFieldNameSubParts())){
                List<String> lengthList = Arrays.asList(dto.getFieldNameSubParts().split(","));
                List<String> fieldName = new ArrayList<>();
                for (int i = 0; i < dto.getFieldNames().size(); i++) {
                    String number = i < lengthList.size() ? lengthList.get(i).equals("0") ? "" : lengthList.get(i) : "";
                    String length = StringUtils.isNotBlank(number) ? String.format("(%s)", number) : number;
                    String format = String.format("%s%s", dto.getFieldNames().get(i).getFieldName(), length);
                    fieldName.add(format);
                }
                String collect = fieldName.stream().collect(Collectors.joining(","));
                key.setFieldIds(collect);
            }else {
                String fields = dto.getFieldNames().stream().map(f -> f.getFieldName()).collect(Collectors.joining(","));
                key.setFieldIds(fields);
            }
            list.add(key);
        }
        return list;
    }

    /**
     * 创建 hive 语句
     * @return
     */
    public static String createHive(ModelTableInfoDto dto, List<DapDataModelTablePartition> partitions){

        StringBuffer hive = new StringBuffer();
        String schema = StringUtils.isNotBlank(dto.getTableSchema()) ? dto.getTableSchema()+"." : "";
        hive.append("create table ").append(schema).append("`").append(dto.getEngName()).append("`").append("(");
        if(!dto.getFieldList().isEmpty()){
            dto.getFieldList().forEach(d -> {
                hive.append("`").append(d.getFieldName()).append("` ").append(d.getDataType());
                // 是否为复合类型定义
                if(checkIsCompoundType(d.getDataType())){
                    hive.append(d.getCompoundType());
                }else {
                    // 有小数
                    if(!isNull(d.getDecimalLength()) && StringUtils.isNotBlank(d.getLength())){
                        hive.append("(").append(d.getLength()).append(",").append(d.getDecimalLength()).append(")");
                    }else {
                        // 无小数
                        hive.append(StringUtils.isNotBlank(d.getLength())? "("+d.getLength() + ")": "");
                    }
                }
                hive.append(",");
            });
            hive.deleteCharAt(hive.length() - 1);
        }
        hive.append(")");
        if(!partitions.isEmpty()){
            hive.append(" PARTITIONED BY (");
            partitions.forEach(p -> {
                hive.append(p.getPartitionFieldName()).append(" ").append(p.getPartitionFieldType() == 1 ? "String":"Integer")
                        .append(StringUtils.isNotBlank(p.getName()) ? " comment '"+ p.getName() + "'" : "").append(",");
            });
            hive.deleteCharAt(hive.length() - 1).append(")");
        }
        String s = null != dto.getHiveStoredAs() ? getStoredAs(dto.getHiveStoredAs()) : "";
        hive.append(" STORED AS " + s + "");
        hive.append(StringUtils.isNotBlank
                (dto.getHiveSerdeProps()) ? " WITH SERDEPROPERTIES (" + dto.getHiveSerdeProps() + ")" : "");
        hive.append(StringUtils.isNotBlank
                (dto.getHiveLocation()) ? " LOCATION '" + dto.getHiveLocation() + "'": "");
        hive.append(StringUtils.isNotBlank
                (dto.getHiveTableProps()) ? " TBLPROPERTIES (" + dto.getHiveTableProps() + ")": "");
        return hive.toString();
    }

    /**
     * 验证hive字段是否为复合类型
     * @return
     */
    public static boolean checkIsCompoundType(String type){

        List<String> list = Arrays.asList("array","map","struct", "uniontype");
        return list.contains(type);
    }

    /**
     * 判断数据类型是否为数值类
     * @param dateType
     * @return
     */
    public static Boolean isNumber (String dateType){
        // 日期类
        String num = "tinyint int smallint mediumint bigint decimal double decimal float numeric";
        return num.contains(dateType);
    }

    /**
     * 判断是否为空
     * @param param
     * @return
     */
    public static boolean isNull(Integer param){
        return null == param || param == 0 ? true : false;
    }

    /**
     *
     * @param storesAs
     * @return
     */
    public static String getStoredAs(Integer storesAs){

        String string = storesAs == 1 ?  "TEXTFILE" : storesAs == 2 ? "SEQUENCEFILE" : storesAs == 3 ? "ORC" :
                storesAs == 4 ? "PARQUET" : storesAs == 5 ? "AVRO" : storesAs == 6 ? "RCFILE" : "JSONFILE";
        return string;
    }

    /**
     * 创建沙箱环境数据库
     * @param url
     * @param pwd
     * @param username
     * @return
     */
    public static boolean createDatabase(String url, String pwd, String username){

        String databaseName = "CREATE DATABASE IF NOT EXISTS sql_execute;";
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DriverManager.getConnection(url,username,pwd);
            //创建prepareStatement对象，用于执行SQL
            ps = connection.prepareStatement(databaseName);
            ps.execute();
        } catch (SQLException e) {
            return false;
        } finally {
            try {
                if(ps != null){
                    ps.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public static ResultBean<String> createTable(String url, String pwd, String username, String sql, String tableName){

        Connection connection = null;
        PreparedStatement ps = null;
        PreparedStatement st = null;
        try {
            connection = DriverManager.getConnection(url,username,pwd);
            // 创建prepareStatement对象，用于执行SQL，创建表
            ps = connection.prepareStatement(sql);
            ps.execute();
            // 删除表
            st = connection.prepareStatement("drop table " + tableName);
            st.execute();
        } catch (SQLException e) {
            return ResultBean.error(e.getMessage());
        }finally {
            try {
                if(st != null){
                    st.close();
                }
                if(ps != null){
                    ps.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ResultBean.ok();
    }

    /**
     *
     * @param defaultValue
     * @return
     */
    public static Boolean checkIsDateTime(String defaultValue){

        // 解析通过说明是字符串的日期时间格式
        try {
            DateUtils.parseDate(defaultValue, PARSE_DATE_TIME_PATTERN);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }
}
